package com.netwit.common.controller;

import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import com.netwit.common.service.CommonService;
import com.netwit.common.utils.UUIDUtil;

import org.apache.commons.lang3.time.DateFormatUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.*;

/**
 * 本 Controller 主要针对单表的操作进行了简单的封装，包含如下内容： 多数据源在
 * dbaip.yml中定义，同时表的别名也可以在该文件中定义，未定义的表名直接使用默认数据源及原表名，表别名建议使用复数形式
 * 1、查询：[GET] /data/{表名|表别名},参数:
 *    page: 第几页
 *    size: 每页几行数据，未设置则采用 application.properties 文件中的配置
 *    orderBy: 排序，在列名前加上 +(升序)或-(降序)
 *    fields: 要在结果中返回的字段，字段间以","分隔
 *    where: 以 whr- 或 where- 开头的参数，whr-开头的参数一般用于固定的查询参数，where-开头的参数在前端可根据查询条件进行动态修改
 *           详细用法：
 *               格式：[whr|where]-fieldnames-oper=value
 *                    fieldnames：可以有多个以","分隔的字段名，它们之间的关系为 OR，
 *                    oper：没有时为=，如：whr-uuid=value 代表 where uuid = value
 *                          like, 如：whr-uuid-like=value 代表 where uuid like %value%
 *                          ne 不等于，如：whr-uuid-ne=value 代表 where uuid != value
 *                          isn 为空，如：whr-uuid-isn=value 代表 where uuid is null
 *                          isnot 不为空，如：whr-uuid-isnot=value 代表 where uuid is not null
 *                          lt 小于，如：whr-uuid-lt=value 代表 where uuid < value
 *                          gt 大于，如：whr-uuid-gt=value 代表 where uuid > value
 *                    多个 where|whr-XXX-oper 间的逻辑关系为 AND
 * 2、增加：[POST] /data/{表名|表别名} 或 /data/form/{表名|表别名}: 同时返回增加数据的访问链接
 * 3、单记录查询：[GET] /data/{表名|表别名}/{uuid}|first
 * 4、单记录删除：[DELETE] /data/{表名|表别名}/{uuid}
 * 5、更新数据记录：[PUT] /data/{表名|表别名} 或 /data/form/{表名|表别名}, 前者接收json数据，后者接收form（未实现), 同时返回被修改数据的访问链接
 * 6、对于只支持 POST，不支持 PUT,PATCH,DELETE 时：[POST]/data/{表名|表别名}/{uuid}, 增加头信息 X-HTTP-Method-Override:
 *    POST(增加),
 *    PUT(更新),
 *    DELETE(删除)
 *    不支持 PATCH, OPTIONS, 此功能暂未实现
 * 7、复杂查询：[GET]/data/主表别名/主表中关联字段列表/从表列表/从表中关联字段列表,参数参见1的说明:
 *    例：/data/users/user_code/userAuths,userRoles/user_code,userid?page=1&order=user_code
 *    主表：users = b_user
 *    主表字段：user_code, 可以有多个，但在这个例子中，两个从表都是与这个字段相关联
 *    从表: userAuths = b_user_auths 与主表关联的字段为 user_code
 *    从表：userRoles = b_user_role 与主表 user_code 相对应的字段是 userid
 *    后面的参数都是针对主表的操作,
 *    这个查询可以跨数据库，即主从表可以不在同一个数据库里，只需在 dbapi.yml 中设置即可
 * 以上操作皆以MAP来存储数据，以实体类为数据的操作暂未实现，未对复杂查询进行封装，比较简单的多表查询在性能允许的情况下建议可采用数据库视图的方式进行
 */
@RestController
@CrossOrigin(value = "*")
@RequestMapping("${common.data.uri:/data}")
public class CommonController {

    private Logger logger = LoggerFactory.getLogger(CommonController.class);

    @Autowired
    private CommonService commonService;

    /**
     * 分页查询
     * 
     * @param tableName 查询的表名，可以在 dbapi.yml 中设置表别名，以和真表名对应
     * @param request   内含 fields(有定义此项时一定要包含uuid), page, size, order(+顺序，-倒序),
     *                  其他传入的参数作为where条件，逻辑与关系
     * @return 返回的结果中包含查询结果及分页信息(带page参数查询时才有) 查询结果以 json
     *         数组表示："data":[{...},{...},...]
     *         分页信息："pageInfo":{"isFirst":true,"pageTotal":2,"hasPrev":false,"isLast":false,"pageSize":1,"hasNext":true,"rowCount":2,"page":1}
     */
    @RequestMapping(value = "{table}", method = RequestMethod.GET, produces = "application/json;charset=UTF-8")
    public ResponseEntity list(@PathVariable(value = "table") String tableName, HttpServletRequest request) {
        if (StringUtils.isEmpty(tableName) || isInjectSQL(tableName)) {
            return new ResponseEntity<>("400 不是合法的请求.", HttpStatus.BAD_REQUEST);
        } else {
            Map<String, Object> map = trMap(tableName, request);
            return new ResponseEntity<>(commonService.list(map), HttpStatus.OK);
        }
    }

    /**
     * 添加表数据: method 是 POST
     * 
     * @param tableName   /{table} 中指定的表(别)名，方法参数前需加上@PathVariable注解
     * @param requestBody 接收传入的 json 内容，方法参数前需加上@RequestBody(JSON)，请求的 content-type
     *                    应为 application/json
     * @return 返回一个集合，包含插入的行数affectedRows,新加入的数据的href等
     */
    @RequestMapping(value = "{table}", method = RequestMethod.POST)
    public ResponseEntity insert(@PathVariable(value = "table") String tableName, @RequestBody Map<String, Object> requestBody, HttpServletRequest request) {
        logger.info("insert - " + requestBody.toString());
        return insertData(tableName, requestBody, request);
    }

    /**
     * UUID单记录查询, method 是 GET
     * 
     * @param tableName 表名
     * @param uuid      键值
     * @return 查询结果：{...}
     */
    @RequestMapping(value = "{table}/{uuid}", method = RequestMethod.GET, produces = "application/json;charset=UTF-8")
    public ResponseEntity detail(@PathVariable(value = "table") String tableName, @PathVariable(value = "uuid") String uuid, HttpServletRequest request){
        if (StringUtils.isEmpty(tableName) || isInjectSQL(tableName) || StringUtils.isEmpty(uuid) || isInjectSQL(uuid)){
            return new ResponseEntity<>(HttpStatus.NO_CONTENT);
        } else {
            Map<String, Object> map = trMap(tableName, request);
            map.put("whr", "uuid-" + uuid);
            Map<String, Object> res = commonService.getOne(map);
            return new ResponseEntity<>(res, HttpStatus.OK);
        }
    }

    /**
     * 修改数据, method 是 PUT
     * 
     * @param tableName   表名或表别名
     * @param uuid        要更新的信息的唯一键值
     * @param requestBody 必须包含 uuid 值
     * @param request     http request
     * @return 返回一个集合，包含更新的行数affectedRows,更新后的数据的href等
     */
    @RequestMapping(value = "{table}/{uuid}", method = RequestMethod.PUT)
    public ResponseEntity update(@PathVariable(value = "table") String tableName, @PathVariable(value = "uuid") String uuid, @RequestBody Map<String, Object> requestBody, HttpServletRequest request){
        return updateData(tableName, uuid, requestBody, request);
    }

    /**
     * 删除记录, method 是 DELETE
     * 
     * @param tableName 表名
     * @param uuid      键值
     * @return 结果：{"tableName":"..", "uuid":"..", "affectedRows":0}
     */
    @RequestMapping(value = "{table}/{uuid}", method = RequestMethod.DELETE)
    public ResponseEntity delete(@PathVariable(value = "table") String tableName, @PathVariable(value = "uuid") String uuid) {
        return deleteData(tableName, uuid);
    }

    /**
     * UUID单记录查询, method 是 GET
     * 
     * @param tableName 表名
     * @return 查询结果：{...}
     */
    @RequestMapping(value = "{table}/first", method = RequestMethod.GET, produces = "application/json;charset=UTF-8")
    public ResponseEntity getOne(@PathVariable(value = "table") String tableName, HttpServletRequest request) {
        if (StringUtils.isEmpty(tableName) || isInjectSQL(tableName)) {
            return new ResponseEntity<>(HttpStatus.NO_CONTENT);
        } else {
            Map<String, Object> map = trMap(tableName, request);
            Map<String, Object> res = commonService.getOne(map);
            return new ResponseEntity<>(res, HttpStatus.OK);
        }
    }

    /**
     * 较为复杂的主从表查询
     *
     * @param tableName 主表别名或表名，在 dbapi.yml 文件中设置
     * @param keyFields 主表与从表的关联字段，可以有多个，分别对应每一个子表，以 "," 号分隔，如果都一样可以只定义一个
     * @param childes   从表，可以有多个，以 "," 号分隔
     * @param relFields 从表与主表的关联字段，可以有多个，分别对应每一个子表，以 "," 号分隔，如果都一样可以只定义一个
     * @param request   其中可包含一些查询参数，和 map 一样，除 fields, order, limit, page, size
     *                  外都当成查询参数，多个相同变量名的会构造成逻辑或的关系（未测试，慎用）
     * @return 结果集
     */
    @SuppressWarnings("unchecked")
    @RequestMapping(value = "{table}/{keyFields}/{childes}/{relFields}", method = RequestMethod.GET, produces = "application/json;charset=UTF-8")
    public ResponseEntity query(@PathVariable(value = "table") String tableName, @PathVariable(value = "keyFields") String keyFields, @PathVariable(value = "childes") String childes, @PathVariable(value = "relFields") String relFields, HttpServletRequest request){
        if (StringUtils.isEmpty(tableName) || isInjectSQL(tableName)){
            return new ResponseEntity<>(HttpStatus.NO_CONTENT);
        } else {
            Map<String, Object> map = trMap(tableName, request);
            Map<String, Object> result = commonService.list(map);
            String apiPath = (String) map.get("apiPath");

            List<Map<String, Object>> res = (List<Map<String, Object>>) result.get("data");

            String[] keyField = keyFields.replaceAll("[,，]+", ",").split("[,]");
            String[] child = childes.replaceAll("[,，]+", ",").split("[,]");
            String[] relField = relFields.replaceAll("[,，]+", ",").split("[,]");

            if (keyField.length > 0 && child.length > 0 && relField.length > 0) {
                int length = child.length;
                for (Map<String, Object> m1 : res) {
                    for (int i = 0; i < length; i++) {
                        m1.put(child[i], this.getChild(child[i], apiPath, relField[(i < relField.length ? i : 0)],
                                (String) m1.get(keyField[(i < keyField.length ? i : 0)])));
                    }
                }
            }
            result.replace("data", res);

            return new ResponseEntity<>(result, HttpStatus.OK);
        }
    }

    @SuppressWarnings("unchecked")
    @RequestMapping(value = "{table}/multi", method = RequestMethod.POST, produces = "application/json;charset=UTF-8")
    public ResponseEntity batchInsertUpdateMaps(@PathVariable(value = "table") String tableName, @RequestBody Map<String, Object> requestBody, HttpServletRequest request){
        if (StringUtils.isEmpty(tableName) || isInjectSQL(tableName)) {
            return new ResponseEntity<>("400 非法的请求.", HttpStatus.BAD_REQUEST);
        } else {
            Map<String, Object> map = trMap(tableName, request);

            map.put("alias", tableName);
            ArrayList<Map<String, Object>> rows = (ArrayList<Map<String, Object>>) requestBody.get("rows");
            for (Map<String, Object> row : rows) {
                // 生成uuid, create_time, join_time, join_code
                if (row.containsKey("uuid") && StringUtils.isEmpty(row.get("uuid")))
                    row.replace("uuid", UUIDUtil.getUUID32());
                if (row.containsKey("create_time") && StringUtils.isEmpty(row.get("create_time")))
                    row.replace("create_time", formatNormalDateString());
                if (row.containsKey("createtime") && StringUtils.isEmpty(row.get("createtime")))
                    row.replace("createtime", formatNormalDateString());
                if (row.containsKey("createTime") && StringUtils.isEmpty(row.get("createTime")))
                    row.replace("createTime", formatNormalDateString());
                if (row.containsKey("joinTime") && StringUtils.isEmpty(row.get("joinTime")))
                    row.replace("joinTime", formatNormalDateString());
                if (row.containsKey("jointime") && StringUtils.isEmpty(row.get("jointime")))
                    row.replace("jointime", formatNormalDateString());
                if (row.containsKey("join_time") && StringUtils.isEmpty(row.get("join_time")))
                    row.replace("join_time", formatNormalDateString());
                if (row.containsKey("join_code") && StringUtils.isEmpty(row.get("join_code")))
                    row.replace("join_code", getRandom(4));
                if (row.containsKey("joincode") && StringUtils.isEmpty(row.get("joincode")))
                    row.replace("joincode", getRandom(4));
                if (row.containsKey("joinCode") && StringUtils.isEmpty(row.get("joinCode")))
                    row.replace("joinCode", getRandom(4));
            }
            map.put("list", rows);
            Integer affectedRows = commonService.batchInsertUpdateMaps(map);
            if (affectedRows <= 0) map.put("message", "数据未插入或更新，可能包含有不合法信息.");

            return new ResponseEntity<>(map, affectedRows <= 0 ? HttpStatus.BAD_REQUEST : HttpStatus.OK);
        }
    }

     /**
     * 添加表数据: method 是 POST
     * @param tableName /{table} 中指定的表(别)名，方法参数前需加上@PathVariable注解
     * @param params 接收传入的表单内容，方法参数前需加上@RequestParam(FORM)，请求的 content-type 应为 application/x-www-form-urlencoded
     * @return ResponseEntity
     */
     @RequestMapping(value = "form/{table}", method = RequestMethod.POST)
     public ResponseEntity insertForm(@PathVariable(value = "table") String tableName, @RequestParam Map<String, Object> params, HttpServletRequest request){
         return insertData(tableName, params, request);
     }

     @RequestMapping(value = "form/{table}", method = RequestMethod.PUT)
     public ResponseEntity updateForm(@PathVariable(value = "table") String tableName, @RequestParam Map<String, Object> params, HttpServletRequest request){
         String uuid = request.getParameter("uuid");
         if (StringUtils.isEmpty(uuid)) {
             uuid = (String) params.get("uuid");
         }
         return updateData(tableName, uuid, params, request);
     }

    /**
     * 当浏览器不支持HTTP的PUT,DELETE等方法时可调用以下两个方法，以下两个方法分别支持JSON及FORM数据
     * 在Request的头信息中加入 X-HTTP-Method-Override 信息，为空时代表POST(增加)，另外两个为PUT(更新),DELETE(删除)，
     * 其他方法不支持，为非法
     * @param tableName
     * @param requestBody
     * @param request
     * @return
     */
     @RequestMapping(value = "m/{table}", method = RequestMethod.POST)
     public ResponseEntity maintain(@PathVariable(value = "table") String tableName, @RequestBody Map<String, Object> requestBody, HttpServletRequest request){
         return maintainData(tableName, requestBody, request);
     }

     @RequestMapping(value = "mf/{table}", method = RequestMethod.POST)
     public ResponseEntity maintainForm(@PathVariable(value="table")String tableName, @RequestParam Map<String,Object> params, HttpServletRequest request) {
         return maintainData(tableName, params, request);
     }

     private void setTime(Map<String, Object> map) {
         // 加入创建时间之类的
         for (Map.Entry<String, Object> param : map.entrySet()) {
             if (param.getKey().toLowerCase().endsWith("time")) {
                 if (StringUtils.isEmpty(param.getValue())) {
                     map.put(param.getKey(), formatNormalDateString());
                 }
             }
         }
     }

     private ResponseEntity insertData(String tableName, Map<String, Object> params, HttpServletRequest request){
         if (StringUtils.isEmpty(tableName) || isInjectSQL(tableName)) {
             return new ResponseEntity<>("400 不是合法的请求.", HttpStatus.BAD_REQUEST);
         } else {
             Map<String, Object> map = trMap(tableName, request);

             Integer affectedRows;
             if (params.containsKey("rows")) {
                 // params中如果有rows，则为批量插入
                 ArrayList<Map<String, Object>> rows = (ArrayList<Map<String, Object>>) params.get("rows");
                 for (Map<String, Object> row : rows) {
                     // 生成uuid, create_time, join_time, join_code
                     if (row.containsKey("uuid") && StringUtils.isEmpty(row.get("uuid"))) row.replace("uuid", UUIDUtil.getUUID32());
                     setTime(row);
                 }
                 map.put("list", rows);
                 affectedRows = commonService.batchInsertUpdateMaps(map);
             } else {
                 // 表中有 uuid 字段时，前端的输入即使为空也要有该字段传入
                 String uuid = UUIDUtil.getUUID32();
                 if (map.containsKey("uuid")) map.replace("uuid", uuid);
                 if (params.containsKey("uuid")) params.replace("uuid", uuid);

                 setTime(params);
                 map.put("requestBody", params);
                 affectedRows = commonService.insert(map);
             }
             map.put("affectedRows", affectedRows);
             if (affectedRows < 0) map.put("message", "数据未插入或更新，可能包含有不合法信息.");

             return new ResponseEntity<>(map, affectedRows < 0 ? HttpStatus.BAD_REQUEST : HttpStatus.OK);
         }
     }

     private ResponseEntity updateData(String tableName, String uuid, Map<String, Object> params, HttpServletRequest request){
         if (StringUtils.isEmpty(tableName) || isInjectSQL(tableName) || StringUtils.isEmpty(uuid) || isInjectSQL(uuid)){
             return new ResponseEntity<>("400 非法的请求.", HttpStatus.BAD_REQUEST);
         } else {
             Map<String, Object> map = trMap(tableName, request);
             if (!params.containsKey("whr")) {
                 params.put("whr", "uuid-" + uuid);
             } else {
                 params.replace("whr", "uuid-" + uuid);
             }
             map.put("requestBody", params);

             Integer affectedRows = commonService.update(map);
             map.put("affectedRows", affectedRows);
             if (affectedRows <= 0) map.put("errorMessage", "可能输入的内容不合要求,数据未更新.");

             return new ResponseEntity<>(map, affectedRows <= 0 ? HttpStatus.BAD_REQUEST : HttpStatus.OK);
         }
     }

     private ResponseEntity deleteData(String tableName, String uuid) {
         if (StringUtils.isEmpty(tableName) || isInjectSQL(tableName) || StringUtils.isEmpty(uuid) || isInjectSQL(uuid)) {
             return new ResponseEntity<>("400 非法的请求.", HttpStatus.BAD_REQUEST);
         } else {
             Map<String, Object> map = new LinkedHashMap<>();
             map.put("alias", tableName);
             map.put("whr", "uuid-" + uuid);

             Integer affectedRows = commonService.delete(map);
             map.put("affectedRows", affectedRows.toString());
             if (affectedRows <= 0) map.put("message", "数据未删除成功.");

             return new ResponseEntity<>(map, affectedRows <= 0 ? HttpStatus.BAD_REQUEST : HttpStatus.OK);
         }
     }

     private ResponseEntity maintainData(String tableName, Map<String, Object> params, HttpServletRequest request){
         String method = request.getHeader("X-HTTP-Method-Override");
         method = StringUtils.isEmpty(method) ? RequestMethod.POST.name() : method;

         String uuid = (String) params.get("uuid");
         uuid = StringUtils.isEmpty(uuid) ? request.getParameter("uuid") : uuid;

         if (RequestMethod.POST.name().equalsIgnoreCase(method)){
             return insertData(tableName, params, request);
         } else if (RequestMethod.PUT.name().equalsIgnoreCase(method)){
             return updateData(tableName, uuid, params, request);
         } else if (RequestMethod.DELETE.name().equalsIgnoreCase(method)){
             return deleteData(tableName, uuid);
         } else {
             return new ResponseEntity<>("401 非法的 HTTP 方法.", HttpStatus.BAD_REQUEST);
         }
     }

    private boolean isInjectSQL(String source) {
        String oSource = source.toLowerCase();
        return oSource.contains("delete from") || oSource.contains("drop ") || oSource.contains("select ")
                || oSource.contains("update ");
    }

    @SuppressWarnings("unchecked")
    private List<Map<String, Object>> getChild(String tableName, String apiPath, String field, String value) {
        Map<String, Object> map = new LinkedHashMap<>();
        map.put("alias", tableName);
        map.put("apiPath", apiPath);
        map.put("whr-" + field, value);
        List<Map<String, Object>> res = (List<Map<String, Object>>) commonService.list(map).get("data");
        return res;
    }

    @Value("${common.data.uri:/data}")
    private String dataPath;

    private String apiPath() {
        // return this.getClass().getAnnotation(RequestMapping.class).value()[0];
        return dataPath;
    }

    @Value("${app.page.pagesize:15}")
    private String psize;

    private Map<String, Object> trMap(String tableName, HttpServletRequest req) {
        Map<String, Object> map = new LinkedHashMap<>();

        // 供service使用，设置map中的tableName值
        map.put("alias", tableName);
        // 将request传入的参数全部集中到一个map中
        for (Map.Entry<String, String[]> param : req.getParameterMap().entrySet()) {
            map.put(param.getKey(), param.getValue().length <= 1 ? param.getValue()[0] : param.getValue());
        }
        if (map.containsKey("page") && !map.containsKey("size")) {
            map.put("size", psize);
        }

        // 以下设置用于生成 link, queryString 中的 page 和 size 去除
        String queryString = req.getQueryString();
        String hostAdd = req.getRequestURL().toString().replace(req.getRequestURI(), "");
        map.put("queryString", StringUtils.isEmpty(queryString) ? "" : queryString.replaceAll("(&*(page|size)=\\d*)", ""));
        map.put("apiPath", hostAdd + req.getContextPath() + apiPath());

        logger.debug("trMap: " + map.toString());

        return map;
    }

    private String formatNormalDateString(Date... dates) {
        Date d = dates.length > 0 ? dates[0] : new Date();
        return DateFormatUtils.format(d, "yyyy-MM-dd HH:mm:ss");
    }

    private int getRandom(int length) {
        return (int) ((Math.random() * 9 + 1) * Math.pow(10, length - 1));
    }

}
